Java Database Programming with JDBC Java Database Programming with JDBC
by Pratik Patel
Coriolis, The Coriolis Group
ISBN: 1576100561   Pub Date: 10/01/96
  

Previous Table of Contents Next


DatabaseMetaData

At over 130 methods, the DatabaseMetaData class is by far the largest. It supplies information about what is supported and how things are supported. It also supplies catalog information such as listing tables, columns, indexes, procedures, and so on. Because the JDBC API specification does an adequate job of explaining the methods contained in this class, and most of them are quite straightforward, we’ll just take a look at how the SimpleText driver implements the getTables catalog method. But first, let’s review the basic steps needed to implement each of the catalog methods (that is, those methods that return a ResultSet):

1.  Create the result columns, which includes the column name, type, and other information about each of the columns. You should perform this step regardless of whether the database supports a given catalog function (such as stored procedures). I believe that it is much better to return an empty result set with only the column information than to raise an exception indicating that the database does not support the function. The JDBC specification does not currently address this issue, so it is open for interpretation.
2.  Retrieve the catalog information from the database.
3.  Perform any filtering necessary. The application may have specified the return of only a subset of the catalog information. You may need to filter the information in the JDBC driver if the database system doesn’t.
4.  Sort the result data per the JDBC API specification. If you are lucky, the database you are using will sort the data in the proper sequence. Most likely, it will not. In this case, you will need to ensure that the data is returned in the proper order.
5.  Return a ResultSet containing the requested information.

The SimpleText getTables method will return a list of all of the text files in the catalog (directory) given. If no catalog is supplied, the default directory is used. Note that the SimpleText driver does not perform all of the steps shown previously; it does not provide any filtering, nor does it sort the data in the proper sequence. You are more than welcome to add this functionality. In fact, I encourage it. One note about column information: I prefer to use a Hashtable containing the column number as the key, and a class containing all of the information about the column as the data value. So, for all ResultSets that are generated, I create a Hashtable of column information that is then used by the ResultSet object and the ResultSetMetaData object to describe each column. Listing 10.19 shows the SimpleTextColumn class that is used to hold this information for each column.

Listing 10.19 The SimpleTextColumn class.

package jdbc.SimpleText;

public class SimpleTextColumn
    extends        Object
{
//------------------------------------------------------------------------
    // Constructor
//------------------------------------------------------------------------
    public SimpleTextColumn(
        String name,
        int type,
        int precision)
    {
        this.name = name;
        this.type = type;
        this.precision = precision;
    }

    public SimpleTextColumn(
        String name,
        int type)
    {
        this.name = name;
        this.type = type;
        this.precision = 0;
    }
    public SimpleTextColumn(
        String name)
    {
        this.name = name;
        this.type = 0;
        this.precision = 0;
    }

    public String name;
    public int type;
    public int precision;
    public boolean searchable;
    public int colNo;
    public int displaySize;
    public String typeName;
}

Note that I have used several constructors to set up various default information, and that all of the attributes are public. To follow object-oriented design, I should have provided a get and set method to encapsulate each attribute, but I chose to let each consumer of this object access them directly. Listing 10.20 shows the code for the getTables method.

Listing 10.20 The getTables method.

//----------------------------------------------------------------------
// getTables - JDBC API
// Get a description of tables available in a catalog
//
// Only table descriptions matching the catalog, schema, table
// name and type criteria are returned. They are ordered by
// TABLE_TYPE, TABLE_SCHEM, and TABLE_NAME.
//
// Each table description has the following columns:
//
//     (1) TABLE_CAT     String => table catalog (may be null)
//    (2) TABLE_SCHEM   String => table schema (may be null)
//    (3) TABLE_NAME    String => table name
//     (4) TABLE_TYPE    String => table type
//              Typical types are "TABLE", "VIEW", "SYSTEM TABLE",
//           "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM"
//    (5) REMARKS       String => explanatory comment on the table
//
// Note: Some databases may not return information for
// all tables.
//
//    catalog            a catalog name; "" retrieves those without a
//                       catalog.
//    schemaPattern    a schema name pattern; "" retrieves those
//                     without a schema.
//    tableNamePattern    a table name pattern.
//    types            a list of table types to include; null returns all
//                     types.
//
// Returns a ResultSet. Each row is a table description.
//----------------------------------------------------------------------
public ResultSet getTables(
     String catalog,
     String schemaPattern,
     String tableNamePattern,
     String types[])
    throws SQLException
{
    if (traceOn()) {
        trace("@getTables(" + catalog + ", " + schemaPattern +
                ", " + tableNamePattern + ")");
    }

    // Create a statement object
    SimpleTextStatement stmt =
                (SimpleTextStatement) ownerConnection.createStatement();

    // Create a Hashtable for all of the columns
    Hashtable columns = new Hashtable();

    add(columns, 1, "TABLE_CAT", Types.VARCHAR);
    add(columns, 2, "TABLE_SCHEM", Types.VARCHAR);
    add(columns, 3, "TABLE_NAME", Types.VARCHAR);
    add(columns, 4, "TABLE_TYPE", Types.VARCHAR);
    add(columns, 5, "REMARKS", Types.VARCHAR);

    // Create an empty Hashtable for the rows
    Hashtable rows = new Hashtable();

    // If any of the parameters will return an empty result set, do so
    boolean willBeEmpty = false;

    // If table types are specified, make sure that 'TABLE' is
    // included. If not, no rows will be returned.

    if (types != null) {
        willBeEmpty = true;
        for (int ii = 0; ii < types.length; ii++) {
            if (types[ii].equalsIgnoreCase("TABLE")) {
                willBeEmpty = false;
                break;
            }
        }
    }
    if (!willBeEmpty) {
        // Get a Hashtable with all tables
        Hashtable tables = ownerConnection.getTables(
                    ownerConnection.getDirectory(catalog),
                      tableNamePattern);

        Hashtable singleRow;
        SimpleTextTable table;

        // Create a row for each table in the Hashtable
        for (int i = 0; i < tables.size(); i++) {
            table = (SimpleTextTable) tables.get(new Integer(i));

            // Create a new Hashtable for a single row
            singleRow = new Hashtable();

            // Build the row
            singleRow.put(new Integer(1), new CommonValue(table.dir));
            singleRow.put(new Integer(3), new CommonValue(table.name));
           singleRow.put(new Integer(4), new CommonValue("TABLE"));

            // Add it to the row list
            rows.put(new Integer(i + 1), singleRow);
        }
    }

    // Create the ResultSet object and return it
    SimpleTextResultSet rs = new SimpleTextResultSet();

    rs.initialize(stmt, columns, rows);

    return rs;
}


Previous Table of Contents Next